/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package onpu_dms_xls.kernel;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.io.*;
import java.util.Calendar;

import onpu_dms_xls.exceptions.MyException;
import onpu_dms_xls.exceptions.MySQLException;
import onpu_dms_xls.exceptions.MyXLSException;
import onpu_dms_xls.exceptions.MyXMLException;
import onpu_dms_xls.kernel.common.logAndDebug.LoggingFacade;
import org.apache.log4j.Logger;

/**
 *
 * @author snbm
 */
public class DBTemplatesLoader extends TemplatesLoader {

    

    @Override
    public void loadTemplates(
            Connection con,
            File xlsTemplate,
            File xmlTemplate,
            int documentClassId,
            int userId) throws MyException {
        /*
         * /////+1. get all templates (which have xls and xml file) from templates folder
         * +2. connect to DB.
         * +3. run createTable sql file
         * +4. load XML + XLS files to DB.
         * +5. close connection to DB
         */

        //1
//        String[] templates = getAllTemplates(pathToTemplatesFolder);
//        LoggingFacade.trace.info("all templates in "+pathToTemplatesFolder);
//        for (String s : templates){
//            LoggingFacade.trace.info(s);
//        }

        //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!CHECK FOR NAMES!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!check for rigth files!!!!!!!!!!!!!!!!!!!!!!!!!

        //2
        //DBConnect dbc = new DBConnect(DB_URL, DB_Login, DB_Password);
        Connection conn = con;

        //3
        //runCreateTemplatesFolderFile(pathToTemplatesFolder, conn);

        //4
        LoggingFacade.trace.debug("call");
        loadTemplatesFilesToDB(xlsTemplate, xmlTemplate, conn, documentClassId, userId);

        //5
        //dbc.closeConnection();

        LoggingFacade.trace.info("End of loadTemplates");
    }

    private String[] getAllTemplates(String pathToTemplatesFolder) throws MyException {
        ArrayList<String> templates = new ArrayList<String>();

        File templatesFolder = new File(pathToTemplatesFolder);
        if (!templatesFolder.isDirectory()) {
            throw new MyXMLException(
                    "TemplatesLoader",
                    "loadTemplates",
                    "the object: " + pathToTemplatesFolder + " is not a folder",
                    "");
        }

        String[] allFilesInDirectory = templatesFolder.list();

        //get all XML files name, which have corresponding XLS files
        for (int i = 0; i < allFilesInDirectory.length; i++) {
            String fileName = allFilesInDirectory[i];
            if (fileName.contains(".xml")) {
                String templateName = fileName.replace(".xml", "");
                //check for xls file
                for (int j = 0; j < allFilesInDirectory.length; j++) {
                    if (allFilesInDirectory[j].equals(templateName + ".xls")) {
                        templates.add(templateName);
                        break;
                    }
                }
            }
        }
        return templates.toArray(new String[]{});
    }

    private void runCreateTemplatesFolderFile(String pathToTemplatesFolder, Connection conn) throws MyException {
        String fileName = pathToTemplatesFolder + "/" + "create_templates_table.sql";
        StringBuilder fileContent = new StringBuilder();
        try {
            File queryFile = new File(fileName);
            BufferedReader reader = new BufferedReader(new FileReader(queryFile));

            String text = null;
            while ((text = reader.readLine()) != null) {
                fileContent.append(text).append("\n");
            }

        } catch (IOException e) {
            throw new MyXMLException(
                    "TemplatesLoader",
                    "runCreateTemplatesFolderFile",
                    "while opening file with create table query: " + fileName,
                    e.getMessage());
        }

        try {
            Statement st = conn.createStatement();
            st.executeUpdate(fileContent.toString());
            st.close();
        } catch (SQLException e) {
            throw new MySQLException(
                    "TemplatesLoader",
                    "runCreateTemplatesFolderFile",
                    fileContent.toString(),
                    "",
                    e.getMessage());
        }
    }

    private void loadTemplatesFilesToDB(File xlsTemplate, File xmlTemplate,
            Connection conn, int documentClassId, int userId) throws MyException {

        Calendar cal = Calendar.getInstance();
        int year = cal.get(Calendar.YEAR);
        int month = cal.get(Calendar.MONTH);
        int day = cal.get(Calendar.DAY_OF_MONTH);
        StringBuilder sb = new StringBuilder();
        String date = sb.append(day).append(".").append(month).append(".").append(year).toString();
        LoggingFacade.trace.debug("date: " + date);


        String templateName = xlsTemplate.getName();
        templateName = templateName.substring(0, templateName.length() - 4);
        LoggingFacade.trace.debug("templateName(from file name):" + templateName);

        int maxId = 0;
        String query1 = null;
        try {
            PreparedStatement ps;
            query1 = "select max(id) from documents.doc_templates";
            ps = conn.prepareStatement(query1);
            ResultSet rs = ps.executeQuery();
            rs.next();
            maxId = rs.getInt(1);
            LoggingFacade.trace.debug("max ID (in documents.doc_templates): " + maxId);
        } catch (SQLException e) {
            throw new MySQLException(
                    "TemplatesLoader",
                    "loadTemplatesFilesToDB",
                    query1,
                    "",
                    e.getMessage());
        }

        String query = null;
        LoggingFacade.trace.debug("Working with template: " + templateName);
        try {

            xls:
            {
                LoggingFacade.trace.debug("Working with xls template file.");
                //first, select to check for existence
                PreparedStatement selectPrSt1;
                query = "select * from documents.doc_templates where class_id = ? and doc_type = \'X\' and file_name = ?";
                selectPrSt1 = conn.prepareStatement(query);
                selectPrSt1.setInt(1, documentClassId);
                selectPrSt1.setString(2, templateName);
                LoggingFacade.trace.debug("executing query: " + selectPrSt1.toString());
                ResultSet selectRS1 = selectPrSt1.executeQuery();
                LoggingFacade.trace.debug("executed select to find existing document");
                if (selectRS1.next()) {
                    //update xml file to DB
                    LoggingFacade.trace.info("Trying update since got more then 0 row from query: " + query);
                    PreparedStatement prst2;
                    query = "update documents.doc_templates set file_content = ? where class_id = ? and doc_type = \'X\' and file_name = ? and user_id = ?";
                    prst2 = conn.prepareStatement(query);
                    LoggingFacade.trace.debug("xls template file path: " + xlsTemplate.getAbsolutePath());
                    FileInputStream xlsFIS;
                    xlsFIS = new FileInputStream(xlsTemplate);
                    prst2.setBinaryStream(1, xlsFIS, (int) xlsTemplate.length());

                    prst2.setInt(2, documentClassId);
                    prst2.setString(3, templateName);
                    prst2.setInt(4, userId);
                    prst2.execute();

                    xlsFIS.close();
                    prst2.close();
                    LoggingFacade.trace.debug("Query executed successfully");
                    break xls;
                }
                LoggingFacade.trace.info("Query: " + query + " returned 0 rows, trying insert");
                //set xls file to DB
                maxId++;
                PreparedStatement prst;
                query = "insert into documents.doc_templates values (?,?,?,?,?,?,?)";
                prst = conn.prepareStatement(query);
                LoggingFacade.trace.debug("xls template file path: " + xlsTemplate.getAbsolutePath());
                FileInputStream xlsFIS;
                xlsFIS = new FileInputStream(xlsTemplate);
                prst.setInt(1, maxId);
                prst.setInt(2, documentClassId);
                prst.setString(3, "X");
                prst.setString(4, templateName);
                prst.setBinaryStream(5, xlsFIS, (int) xlsTemplate.length());
                prst.setString(6, date);
                prst.setInt(7, userId);
                prst.execute();
                xlsFIS.close();
                prst.close();
                LoggingFacade.trace.debug("Query executed successfully");
            }

            xml:
            {
                LoggingFacade.trace.debug("Working with xml template file.");
                //first, select to check for existence
                PreparedStatement selectPrSt;
                query = "select * from documents.doc_templates where class_id = ? and doc_type = \'T\' and file_name = ?";
                selectPrSt = conn.prepareStatement(query);
                selectPrSt.setInt(1, documentClassId);
                selectPrSt.setString(2, templateName);
                ResultSet selectRS = selectPrSt.executeQuery();
                LoggingFacade.trace.debug("executed select to find existing document");
                if (selectRS.next()) {
                    //update xml file to DB
                    LoggingFacade.trace.info("Trying update since got more then 0 row from query: " + query);
                    PreparedStatement prst;
                    query = "update documents.doc_templates set file_content = ? where class_id = ? and doc_type = \'T\' and file_name = ? and user_id = ?";
                    prst = conn.prepareStatement(query);
                    LoggingFacade.trace.debug("xml template file path: " + xmlTemplate.getAbsolutePath());

                    FileInputStream xmlFIS;
                    xmlFIS = new FileInputStream(xmlTemplate);
                    prst.setBinaryStream(1, xmlFIS, (int) xmlTemplate.length());
                    prst.setInt(2, documentClassId);
                    prst.setString(3, templateName);
                    prst.setInt(4, userId);
                    prst.execute();
                    xmlFIS.close();
                    prst.close();
                    LoggingFacade.trace.debug("Query executed successfully");
                    break xml;
                }

                LoggingFacade.trace.info("Query: " + query + " returned 0 rows, trying insert");
                //insert xml file to DB
                maxId++;
                PreparedStatement prst;
                query = "insert into documents.doc_templates values (?,?,?,?,?,?,?)";
                prst = conn.prepareStatement(query);
                LoggingFacade.trace.debug("xml template file path: " + xmlTemplate.getAbsolutePath());
                FileInputStream xmlFIS;
                xmlFIS = new FileInputStream(xmlTemplate);
                prst.setInt(1, maxId);
                prst.setInt(2, documentClassId);
                prst.setString(3, "T");
                prst.setString(4, templateName);

                prst.setBinaryStream(5, xmlFIS, (int) xmlTemplate.length());

                prst.setString(6, date);
                prst.setInt(7, userId);
                prst.execute();
                xmlFIS.close();
                prst.close();
                LoggingFacade.trace.debug("Query executed successfully");
            }


        } catch (SQLException e) {
            throw new MySQLException(
                    "TemplatesLoader",
                    "loadTemplatesFilesToDB",
                    query,
                    "",
                    e.getMessage());
        } catch (IOException e) {
            throw new MyXMLException(
                    "TemplatesLoader",
                    "loadTemplatesFilesToDB",
                    "error, while opening xml, xls files of template" + templateName,
                    e.getMessage());
        }

        LoggingFacade.trace.debug("End of loadTemplatesFilesToDB");
    }

    @Override
    public File getTemplateFile(String temporaryFolder, int documentClassId, Connection conn, TemplatesLoaderFiles fileType) throws MyException {
        File templateFile = null;

        LoggingFacade.trace.info("getTemplateFile, temporaryFolder=" + temporaryFolder
                + ", fileType=" + fileType
                + ", file suffix=" + fileType.getFileEnding()
                + ", documentClassId=" + documentClassId);

        String query = null;
        try {
            query = "select file_content from documents.doc_templates where class_id = ? and doc_type = '~';";
            query = query.replace("~", fileType.getDBAppendix());


            /*
            {
            //System.out.println(fc.getSelectedFile());
            File f = new File(fc.getSelectedFile()+name_of_file);
            FileOutputStream fos = new FileOutputStream(f);
            byte[] buffer = new byte[1];
            InputStream is = resultSet.getBinaryStream(2);
            while (is.read(buffer) > 0)
            fos.write(buffer);

            fos.close();
            }
             **/



            LoggingFacade.trace.debug("query: " + query);
            PreparedStatement prst = conn.prepareStatement(query);
            prst.setInt(1, documentClassId);
            ResultSet rs = prst.executeQuery();
            rs.next();
            InputStream is = rs.getBinaryStream(1);

            if (is == null) {
                throw new MySQLException("TemplatesLoader", "getTemplateFileForTemplate",
                        query,
                        "documentClassId= " + documentClassId,
                        "query returned null - NO DATA IN DB!");
            }

            deleteFileInDirectory(documentClassId + fileType.getFileEnding(), temporaryFolder);
            templateFile = new File(temporaryFolder + "/" + documentClassId + fileType.getFileEnding());

            if (!templateFile.createNewFile()) {
                throw new MyXLSException("TemplatesLoader", "getXLSTemplateFileForTemplate", "while creating " + fileType.getFileEnding() + " template file", "");
            }

            FileOutputStream fos = new FileOutputStream(templateFile);

            byte[] buffer = new byte[1000];
            int bytesRead = 0;
            while ((bytesRead = is.read(buffer)) > 0) {
                fos.write(buffer, 0, bytesRead);
            }
            fos.close();
            prst.close();

        } catch (SQLException e) {
            throw new MySQLException("TemplatesLoader", "getTemplateFileForTemplate", query, "", e.getMessage());
        } catch (IOException e) {
            throw new MyXLSException("TemplatesLoader", "getTemplateFileForTemplate", "writing to temporary, " + fileType.getFileEnding() + " template file", e.getMessage());
        }
        return templateFile;
    }

    private void deleteFileInDirectory(String fileName, String directoryName) throws MyException {
        LoggingFacade.trace.debug("trying to delete file:" + fileName + " in directory:" + directoryName);
        File templateDirectory = new File(directoryName);
        String[] filesInTemplateDirectory = templateDirectory.list();
        for (String name : filesInTemplateDirectory) {
            LoggingFacade.trace.debug("one file from directory: " + name);
            if (name.equals(fileName)) {
                LoggingFacade.trace.debug("file name match!: " + name);
                File existingFile = new File(directoryName + "/" + fileName);
                LoggingFacade.trace.debug("file path: " + existingFile.getAbsolutePath());
                if (existingFile.delete()) {
                    LoggingFacade.trace.info("deleted existing template file: " + fileName+ " in directory:" + directoryName);
                    return;
                } else {
                    if (fileName.contains(".xml")) {
                        throw new MyXMLException("TemplatesLoader", "deleteFileInDirectory", "while deleting xml template file", "file not deleted");
                    } else if (fileName.contains(".xls")) {
                        throw new MyXLSException("TemplatesLoader", "deleteFileInDirectory", "while deleting xls template file", "file not deleted");
                    }
                }
            }//if - check file name
        }//for
    }
}
